﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
/// <summary>
/// Summary description for CommonDA
/// </summary>
public class CommonDA
{
    private DataAccess da;
	public CommonDA()
	{
		//
		// TODO: Add constructor logic here
		//
        da = new DataAccess();
	}
    public DataSet GetTop8Design()
    {
        try
        {
            DataSet ds = new DataSet();
            string query = "select top 8 * from Design order by designId desc";
            SqlParameter[] sqlParameters = new SqlParameter[0];
            ds = da.executeSelectQuery(query, sqlParameters);
            return ds;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public DataTable GetImg()
    {
        try
        {
            DataTable ds = new DataTable();
            string query = "select * from Design";
            SqlParameter[] sqlParameters = new SqlParameter[0];
            ds = da.executeSelect(query, sqlParameters);
            return ds;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    public DataTable GetAllCompetition()
    {
        try
        {
            DataTable dt = new DataTable();
            string query = "select * from Competition";
            SqlParameter[] sqlParameters = new SqlParameter[0];
            dt = da.executeSelect(query, sqlParameters);
            return dt;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    public DataTable GetAllCompetitionOrder()
    {
        try
        {
            DataTable dt = new DataTable();
            string query = "select * from Competition order by comId desc";
            SqlParameter[] sqlParameters = new SqlParameter[0];
            dt = da.executeSelect(query, sqlParameters);
            return dt;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    public DataSet GetOneAwards()
    {
        string query = "select top 1 comId,name from Competition where DATEDIFF(D,endDate,GETDATE())>0 order by DATEDIFF(D,startDate,GETDATE())";
        SqlParameter[] sqlParameters = new SqlParameter[0];
        DataSet ds = new DataSet();
        ds = da.executeSelectQuery(query, sqlParameters);
        int id = Convert.ToInt16(ds.Tables[0].Rows[0]["comId"]);
        string name = ds.Tables[0].Rows[0]["name"].ToString();
        string query2 = "select top 3 c.name,m.designId,d.name as designName,AVG(m.mark) as SumMark from Mark m inner join Design d on m.designId=d.designId inner join Competition c on d.comId=c.comId where d.comId=" + id + " group by m.designId,c.name,d.name order by SumMark desc";
        DataSet ds2 = da.executeSelectQuery(query2, sqlParameters);
        /*Competition comp = new Competition();
        DataRow dr = ds.Tables[0].Rows[0];
        comp.ComId = Convert.ToInt32(dr["comId"].ToString());
        comp.Name = dr["name"].ToString();
        comp.FirstPrize = dr["firstPrize"].ToString();
        comp.SecondPrize = dr["secondPrize"].ToString();
        comp.ThirdPrize = dr["thirdPrize"].ToString();
        comp.PostDate = dr["postDate"].ToString();
        comp.StartDate = dr["startDate"].ToString();
        comp.EndDate = dr["endDate"].ToString();
        comp.Description = dr["description"].ToString();
        comp.Quotation = dr["quotation"].ToString();
        comp.Manager.Fullname = dr["managerId"].ToString();
        comp.CompImage = dr["compImage"].ToString();
        */
        return ds2;
    }
    public Competition getTop2Competition()
    {
        string query = "select top 1 c.comId,c.name,c.startDate,c.endDate,c.description,c.compImage,COUNT(d.comId) as countDesign from Competition c inner join Design d on c.comId=d.comId where DATEDIFF(D,startDate,GETDATE())>0 and DATEDIFF(D,endDate,GETDATE())<0 group by c.comId,c.name,c.startDate,c.endDate,c.description,c.compImage order by countDesign desc";
        SqlParameter[] sqlParameters = new SqlParameter[0];
        DataSet ds = new DataSet();
        ds = da.executeSelectQuery(query, sqlParameters);
        Competition comp = new Competition();
        DataRow dr = ds.Tables[0].Rows[0];
        comp.ComId = Convert.ToInt32(dr["comId"].ToString());
        comp.Name = dr["name"].ToString();
        comp.StartDate = dr["startDate"].ToString();
        comp.EndDate = dr["endDate"].ToString();
        comp.Description = dr["description"].ToString();
        comp.CompImage = dr["compImage"].ToString();
        return comp;
    }
    public DataSet getCompetitions()
    {
        try
        {
            DataSet ds = new DataSet();
            string query = "select top 7 * from Competition where DATEDIFF(D,startDate,GETDATE())>0 and DATEDIFF(D,endDate,GETDATE())<0";
            SqlParameter[] sqlParameters = new SqlParameter[0];
            ds = da.executeSelectQuery(query, sqlParameters);
            return ds;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    public Competition getCompetitionUpcomming()
    {
        string query = "select top 1 * from Competition where DATEDIFF(D,startDate,GETDATE())<0";
        SqlParameter[] sqlParameters = new SqlParameter[0];
        DataSet ds = new DataSet();
        ds = da.executeSelectQuery(query, sqlParameters);
        Competition comp = new Competition();
        DataRow dr = ds.Tables[0].Rows[0];
        comp.ComId = Convert.ToInt32(dr["comId"].ToString());
        comp.Name = dr["name"].ToString();
        comp.FirstPrize = dr["firstPrize"].ToString();
        comp.SecondPrize = dr["secondPrize"].ToString();
        comp.ThirdPrize = dr["thirdPrize"].ToString();
        comp.PostDate = dr["postDate"].ToString();
        comp.StartDate = dr["startDate"].ToString();
        comp.EndDate = dr["endDate"].ToString();
        comp.Description = dr["description"].ToString();
        comp.Quotation = dr["quotation"].ToString();
        comp.Manager.Fullname = dr["managerId"].ToString();
        comp.CompImage = dr["compImage"].ToString();
        return comp;
    }
    public Competition getDetailCompetition(string id)
    {
        string query = "select * from Competition where comId = " + id + " ";
        SqlParameter[] sqlParameters = new SqlParameter[1];
        sqlParameters[0] = new SqlParameter("@id", SqlDbType.BigInt);
        sqlParameters[0].Value = Convert.ToString(id);
        DataSet ds = new DataSet();
        ds = da.executeSelectQuery(query, sqlParameters);
        Competition comp = new Competition();
        DataRow dr = ds.Tables[0].Rows[0];
        comp.ComId = Convert.ToInt32(dr["comId"].ToString());
        comp.Name = dr["name"].ToString();
        comp.FirstPrize = dr["firstPrize"].ToString();
        comp.SecondPrize = dr["secondPrize"].ToString();
        comp.ThirdPrize = dr["thirdPrize"].ToString();
        comp.PostDate = dr["postDate"].ToString();
        comp.StartDate = dr["startDate"].ToString();
        comp.EndDate = dr["endDate"].ToString();
        comp.Description = dr["description"].ToString();
        comp.Quotation = dr["quotation"].ToString();
        comp.Manager.Fullname = dr["managerId"].ToString();
        comp.CompImage = dr["compImage"].ToString();
        return comp;
    }
    public Design getDetailDesign(string id)
    {
        string query = "select * from Design join Competition on Design.comId = Competition.comId join Student on Student.studentId = Design.studentId where Design.designId = " + id + " ";
        SqlParameter[] sqlParameters = new SqlParameter[1];
        sqlParameters[0] = new SqlParameter("@id", SqlDbType.BigInt);
        sqlParameters[0].Value = Convert.ToString(id);
        DataSet ds = new DataSet();
        ds = da.executeSelectQuery(query, sqlParameters);
        Design d1 = new Design();
        DataRow dr = ds.Tables[0].Rows[0];
        d1.Id = Convert.ToInt32(dr[0].ToString());
        d1.Name = dr[1].ToString();
        d1.PostDate = dr[2].ToString();
        d1.Description = dr[3].ToString();
        d1.Quotation = dr[4].ToString();
        d1.DesignImage = dr[5].ToString();
        d1.Com.Name = dr[9].ToString();
        d1.St.Fullname = dr[21].ToString();
        return d1;
    }
    public Design getName1Design(string name)
    {
        string query = "select top 1 * from Design join Competition on Design.comId = Competition.comId join Student on Student.studentId = Design.studentId where Competition.name = " + name + " ORDER BY Design.name DESC ";
        SqlParameter[] sqlParameters = new SqlParameter[1];
        sqlParameters[0] = new SqlParameter("@name", SqlDbType.VarChar);
        sqlParameters[0].Value = Convert.ToString(name);
        DataSet ds = new DataSet();
        ds = da.executeSelectQuery(query, sqlParameters);
        Design d1 = new Design();
        DataRow dr = ds.Tables[0].Rows[0];
        d1.Id = Convert.ToInt32(dr[0].ToString());
        d1.Name = dr[1].ToString();
        d1.PostDate = dr[2].ToString();
        d1.Description = dr[3].ToString();
        d1.Quotation = dr[4].ToString();
        d1.DesignImage = dr[5].ToString();
        d1.Com.Name = dr[9].ToString();
        d1.St.Fullname = dr[21].ToString();
        return d1;
    }
}